package agh.rozproszony.DBStorage;

import View.InvitationRow;
import agh.rozproszony.model.Invitation;
import agh.rozproszony.model.Message;
import agh.rozproszony.model.Reservation;
import agh.rozproszony.model.Room;
import agh.rozproszony.model.User;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataBaseManager implements DataBaseEngine {

    private String databaseName;
    private String databaseAddress;
    private String databasePassword;
    private String databaseUser;

    public DataBaseManager() {
        databaseName = "serverDB";
        databaseAddress = "localhost";
        databasePassword = "pass";
        databaseUser = "root";
    }

    public DataBaseManager(String name, String address, String pass, String dataU) {
        databaseName = name;
        databaseAddress = address;
        databasePassword = pass;
        databaseUser = dataU;
    }

    public DataBaseManager(String dbName) {
        databaseName = dbName;
        databaseAddress = "localhost";
        databasePassword = "pass";
        databaseUser = "root";
    }

    public Connection DataBaseConnect() throws SQLException {
        //System.out.println("--------Connection Testing ------------");

        try {

            Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {

            System.out.println("Where is your MySql JDBC Driver? "
                    + "Include in your library path!");
            e.printStackTrace();

            return null;
        }
        Connection connection = null;

        try {

            connection = DriverManager.getConnection("jdbc:mysql://" + databaseAddress + "/" + databaseName + "?useUnicode=true&characterEncoding=UTF-8", databaseUser, databasePassword);

        } catch (SQLException e) {

            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            System.exit(1);

            return null;
        }

        if (connection != null) {
            //System.out.println("Connection Successful");

            return connection;
        } else {
            System.out.println("Failed to make connection!");
        }
        return null;
    }

    @Override
    public User getUser(int id) throws SQLException {

        User myUser = new User();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select UserLogin, UserPass, UserEmail, UserType from user where UserID = " + id;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rsA");
            return null;
        }

        while (rs.next()) {
            myUser.setId(id);
            myUser.setLogin(rs.getString("UserLogin"));
            myUser.setPassword(rs.getString(("UserPass")));
            myUser.setEmail(rs.getString("UserEmail"));
            myUser.setType(rs.getInt("UserType"));
            connection.close();
            //System.out.println("Connection Closed");

            return myUser;
        }

        return null;

    }

    @Override
    public Room getRoom(int roomId) throws SQLException {

        Room myRoom = new Room();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select RoomName, Capacity from Rooms where RoomID = " + roomId;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myRoom.setId(roomId);
            myRoom.setName(rs.getString("RoomName"));
            myRoom.setCapacity(rs.getInt("Capacity"));

            connection.close();
            //System.out.println("Connection Closed");

            return myRoom;
        }

        return null;
    }

    @Override
    public Reservation getReservation(int id) throws SQLException {

        Reservation myReservation = new Reservation();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select ReservationID, RoomID,UserID,Year,Month,Day,FromHour,ToHour from reservations where ReservationID = " + id;

        //String niceQuery= "SELECT reservations.ReservationID, Rooms.RoomName, User.UserLogin, Reservations.Year, Reservations.Month, Reservations.Day, Reservations.FromHour, Reservations.ToHour FROM (Reservations INNER JOIN Rooms ON Reservations.RoomID = Rooms.RoomID) INNER JOIN User ON Reservations.UserID = User.UserID where Reservations.ReservationID= "+id;

        //System.out.println(query);
        ResultSet rs = statement.executeQuery(query);

        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myReservation.setId(rs.getInt("ReservationID"));
            myReservation.setRoomId(rs.getInt("RoomID"));
            myReservation.setOwnerId(rs.getInt("UserID"));
            myReservation.setYear(rs.getInt("Year"));
            myReservation.setMonth(rs.getInt("Month"));
            myReservation.setDay(rs.getInt("Day"));
            myReservation.setFromHour(rs.getInt("FromHour"));
            myReservation.setToHour(rs.getInt("ToHour"));

            connection.close();
            //System.out.println("Connection Closed");

            return myReservation;
        }

        return null;
    }

    @Override
    public Message getMessage(int id) throws SQLException {

        Message myMessage = new Message();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from messages where MessageID= " + id;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myMessage.setId(id);
            myMessage.setTitle(rs.getString("MessageTitle"));
            myMessage.setContent(rs.getString("MessageContent"));
            myMessage.setSenderId(rs.getInt("MessageSender"));
            myMessage.setRecipientId(rs.getInt("MessageRecipient"));


            connection.close();
            //System.out.println("Connection Closed");

            return myMessage;
        }

        return null;
    }

    @Override
    public Invitation getInvitation(int id) throws SQLException {

        Invitation myInvitation = new Invitation();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from invitations where InvitationID= " + id;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myInvitation.setId(id);
            myInvitation.setReservationId(rs.getInt("reservationID"));
            myInvitation.setUserId(rs.getInt("userID"));
            myInvitation.setTitle(rs.getString("title"));
            myInvitation.setAccepted(rs.getBoolean("accepted"));


            connection.close();
            //System.out.println("Connection Closed");

            return myInvitation;
        }

        return null;
    }

    @Override
    public User getUserByLogin(String login) throws SQLException {
        User myUser = new User();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select UserID, UserLogin, UserPass, UserEmail, UserType from user where UserLogin = '" + login + "'";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rsA");
            return null;
        }


        while (rs.next()) {
            myUser.setId(rs.getInt("UserID"));
            myUser.setLogin(rs.getString("UserLogin"));
            myUser.setPassword(rs.getString(("UserPass")));
            myUser.setEmail(rs.getString("UserEmail"));
            myUser.setType(rs.getInt("UserType"));

            connection.close();
            // System.out.println("Connection Closed");

            return myUser;
        }

        return null;
    }

    @Override
    public User getUserByEmail(String email) throws SQLException {
        User myUser = new User();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select UserID, UserLogin, UserPass, UserEmail, UserType from user where UserEmail = '" + email + "'";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rsA");
            return null;
        }


        while (rs.next()) {
            myUser.setId(rs.getInt("UserID"));
            myUser.setLogin(rs.getString("UserLogin"));
            myUser.setPassword(rs.getString(("UserPass")));
            myUser.setEmail(rs.getString("UserEmail"));
            myUser.setType(rs.getInt("UserType"));

            connection.close();
            //System.out.println("Connection Closed");

            return myUser;
        }

        return null;
    }

    @Override
    public void changePassword(User user, String newPassword) throws SQLException {

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "UPDATE user SET UserPass =  '" + newPassword + "' WHERE  UserID =" + user.getId();

            statement.execute(query);

        }
        connection.close();
        System.out.println("Connection Closed");

    }

    @Override
    public int getUserTypeByName(String name) throws SQLException {
        return getUserByLogin(name).getType();
    }

    @Override
    public int getUserTypeByID(int id) throws SQLException {
        return getUser(id).getType();
    }

    @Override
    public Message getMessage(String title) throws SQLException {
        Message myMessage = new Message();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select messages.MessageID,messages.MessageTitle, messages.MessageContent,messages.MessageSender from Messages where Messages.MessageTitle= '" + title + "'";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myMessage.setId(rs.getInt("MessageID"));
            myMessage.setTitle(rs.getString("MessageTitle"));
            myMessage.setContent(rs.getString("MessageContent"));
            myMessage.setSenderId(rs.getInt("MessageSender"));

            connection.close();
            //System.out.println("Connection Closed");

            return myMessage;
        }

        return null;
    }

    public Message getMessageByUser(int UserID) throws SQLException {
        Message myMessage = new Message();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from messages where messagerecipient= " + UserID;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            myMessage.setId(rs.getInt("MessageID"));
            myMessage.setTitle(rs.getString("MessageTitle"));
            myMessage.setContent(rs.getString("MessageContent"));
            myMessage.setSenderId(rs.getInt("MessageSender"));
            myMessage.setRecipientId(rs.getInt("MessageRecipient"));

            connection.close();
            // System.out.println("Connection Closed");

            return myMessage;
        }

        return null;
    }

    @Override
    public ArrayList<Message> getMessages(int UserID) throws SQLException {

        ArrayList<Message> myMessageList = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from messages order by messagerecipient DESC";
        //System.out.println(query);

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {
            Message myMessage = new Message();
            myMessage.setId(rs.getInt("MessageID"));
            myMessage.setTitle(rs.getString("MessageTitle"));
            myMessage.setContent(rs.getString("MessageContent"));
            myMessage.setSenderId(rs.getInt("MessageSender"));
            myMessage.setRecipientId(rs.getInt("MessageRecipient"));

            //System.out.println("Wiadomosc: " + myMessage.getTitle() +" "+ myMessage.getContent());
            myMessageList.add(myMessage);

        }
        connection.close();

        return myMessageList;
    }

    @Override
    public ArrayList<Message> getNotReadedMessages(int userId) throws SQLException {

        ArrayList<Message> myMessageList = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from messages where messagerecipient= " + userId;

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {
            Message myMessage = new Message();
            myMessage.setId(rs.getInt("MessageID"));
            myMessage.setTitle(rs.getString("MessageTitle"));
            myMessage.setContent(rs.getString("MessageContent"));
            myMessage.setSenderId(rs.getInt("MessageSender"));
            myMessage.setRecipientId(rs.getInt("MessageRecipient"));
            myMessage.setReaded(0);

            if (myMessage.getReaded() == 0) {
                myMessageList.add(myMessage);
            }

        }
        connection.close();

        return myMessageList;
    }

    @Override
    public List<Reservation> getRoomReservations(int roomId, int day, int month, int year) throws SQLException {

        List<Reservation> allReservations = new ArrayList();
        List<Reservation> roomReservations = new ArrayList();


        allReservations = getAllReservations();

        for (Reservation reservation : allReservations) {
            if (reservation.getRoomId() == roomId && reservation.getDay() == day && reservation.getMonth() == month && reservation.getYear() == year) {
                roomReservations.add(reservation);
            }
        }
        return roomReservations;

    }

    @Override
    public void clearDatabase() throws SQLException {

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String clearUsers = "truncate table user";
            String clearInvitations = "truncate table invitations";
            String clearMessages = "truncate table messages";
            String clearRooms = "truncate table rooms";
            String clearReservations = "truncate table reservations";


            statement.execute(clearUsers);
            statement.execute(clearInvitations);
            statement.execute(clearMessages);
            statement.execute(clearRooms);
            statement.execute(clearReservations);

        }
        connection.close();
        //System.out.println("Connection Closed");

    }

    @Override
    public void insertUser(User user) throws SQLException {
        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "INSERT INTO  user (UserID ,UserLogin ,UserPass ,UserEmail ,UserType) VALUES (NULL, '" + user.getLogin() + "','" + user.getPassword() + "','" + user.getEmail() + "','" + user.getType() + "')";
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public void insertMessage(Message message) throws SQLException {
        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "INSERT INTO messages (MessageID, MessageTitle, MessageContent, MessageSender, MessageRecipient) VALUES (NULL, '" + message.getTitle() + "','" + message.getContent() + "','" + message.getSenderId() + "','" + message.getRecipientId() + "')";
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public void insertReservation(Reservation reservation) throws SQLException {

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "INSERT INTO reservations (ReservationID, RoomID, UserID, Year, Month, Day, FromHour, ToHour) VALUES (NULL, '" + reservation.getRoomId() + "','" + reservation.getOwnerId() + "','" + reservation.getYear() + "','" + reservation.getMonth() + "','" + reservation.getDay() + "','" + reservation.getFromHour() + "','" + reservation.getToHour() + "')";
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public void insertRoom(Room room) throws SQLException {

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "INSERT INTO  rooms (RoomID ,RoomName ,Capacity) VALUES (NULL, '" + room.getName() + "','" + room.getCapacity() + "')";
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public List<Room> getAllRooms() throws SQLException {

        List<Room> myRoomsList = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from rooms";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            Room myRoom = new Room();
            myRoom.setId(rs.getInt("RoomID"));
            myRoom.setName(rs.getString("RoomName"));
            myRoom.setCapacity((rs.getInt("Capacity")));

            myRoomsList.add(myRoom);

        }
        connection.close();

        return myRoomsList;
    }

    @Override
    public List<User> getAllUsers() throws SQLException {
        List<User> myUsersList = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from user";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            User myUser = new User();
            myUser.setId(rs.getInt("UserID"));
            myUser.setLogin(rs.getString("UserLogin"));
            myUser.setPassword(rs.getString(("UserPass")));
            myUser.setEmail(rs.getString("UserEmail"));
            myUser.setType(rs.getInt("UserType"));

            myUsersList.add(myUser);


        }
        connection.close();

        return myUsersList;
    }

    @Override
    public List<Reservation> getAllReservations() throws SQLException {

        List<Reservation> myReservationList = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from reservations";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            Reservation myReservation = new Reservation();

            myReservation.setId(rs.getInt("ReservationID"));
            myReservation.setRoomId(rs.getInt("RoomID"));
            myReservation.setOwnerId(rs.getInt("UserID"));
            myReservation.setYear(rs.getInt("Year"));
            myReservation.setMonth(rs.getInt("Month"));
            myReservation.setDay(rs.getInt("Day"));
            myReservation.setFromHour(rs.getInt("FromHour"));
            myReservation.setToHour((rs.getInt("ToHour")));

            myReservationList.add(myReservation);


        }
        connection.close();

        return myReservationList;
    }

    @Override
    public boolean isReservationPossible(Reservation reservation) {

        List<Reservation> allReservations = new ArrayList();

        try {
            allReservations = getAllReservations();
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        for (Reservation existingReservation : allReservations) {
            // Jezeli rezerwacja jest na ten sam pokoj i dzien...
            if (existingReservation.getRoomId() == reservation.getRoomId()
                    && existingReservation.getYear() == reservation.getYear()
                    && existingReservation.getMonth() == reservation.getMonth()
                    && existingReservation.getDay() == reservation.getDay()) {

                // Jezeli poczatek rezerwacji jest w zajetym przedziale, zwroc false
                if (reservation.getFromHour() >= existingReservation.getFromHour()
                        && reservation.getFromHour() <= existingReservation.getToHour()) {
                    return false;
                }

                // Jezeli koniec rezerwacji jest w zajetym przedziale, zwroc false
                if (reservation.getToHour() >= existingReservation.getFromHour()
                        && reservation.getToHour() <= existingReservation.getToHour()) {
                    return false;
                }

                // Jezeli istniejaca rezerwacja jest wewnatrz dodawanej, zwroc false
                if (reservation.getFromHour() < existingReservation.getFromHour()
                        && reservation.getToHour() > existingReservation.getToHour()) {
                    return false;
                }
            }
        }

        return true;
    }

    @Override
    public void insertInvitiation(Invitation invitation) throws SQLException {

        Connection connection = null;
        Statement statement = null;
        int accepted = 0;

        if (invitation.isAccepted() == true) {
            accepted = 1;
        } else {
            accepted = 0;
        }

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "INSERT INTO invitations (InvitationID, reservationID ,userID ,title, accepted) VALUES (NULL , '" + invitation.getReservationId() + "','" + invitation.getUserId() + "','" + invitation.getTitle() + "','" + accepted + "')";
            System.out.println(query);
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public ArrayList<Invitation> getAwaitingInvitations(int userId) throws SQLException {

        ArrayList<Invitation> myAwaitingInvitations = new ArrayList();

        Connection connection = null;
        Statement statement = null;
        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select invitations.* from invitations,reservations where "
                + " invitations.reservationID = reservations.ReservationID  AND "
                + " DATE( FROM_UNIXTIME( UNIX_TIMESTAMP( CONCAT( reservations.Year, '-', LPAD( reservations.Month , 2, '00' ) , '-', LPAD( reservations.Day , 2, '00' ) ) ) ) ) >= DATE(NOW()) "
                + " AND invitations.userID= " + userId + " and invitations.accepted = 0";

        ResultSet rs = statement.executeQuery(query);
        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {
            Invitation myInvitation = new Invitation();
            myInvitation.setId(rs.getInt("InvitationID"));
            myInvitation.setReservationId(rs.getInt("ReservationID"));
            myInvitation.setUserId(rs.getInt("UserID"));
            myInvitation.setTitle(rs.getString("title"));
            myInvitation.setAccepted(false);

            myAwaitingInvitations.add(myInvitation);

        }
        connection.close();

        return myAwaitingInvitations;
    }

    @Override
    public String getReservationRoomName(int id) throws RemoteException, SQLException {

        Reservation myReservation = new Reservation();
        myReservation = getReservation(id);

        Room myRoom = new Room();
        myRoom = getRoom(myReservation.getRoomId());

        return myRoom.getName();

    }

    @Override
    public String getReservationOwnerLogin(int id) throws RemoteException, SQLException {

        Reservation myReservation = new Reservation();
        myReservation = getReservation(id);

        User myUser = new User();
        myUser = getUser(myReservation.getOwnerId());

        return myUser.getLogin();
    }

    @Override
    public void deleteReservation(Reservation reservation) throws SQLException {
        Connection connection = null;
        Statement statement = null;


        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "DELETE FROM reservations WHERE RoomID = '" + reservation.getRoomId() + "' and UserID = '" + reservation.getOwnerId() + "' and Year = '" + reservation.getYear() + "' and Month = '" + reservation.getMonth() + "' and Day ='" + reservation.getDay() + "' and FromHour = '" + reservation.getFromHour() + "' and ToHour ='" + reservation.getToHour() + "'";
            System.out.println(query);
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public void deleteUser(User user) throws SQLException {

        Connection connection = null;
        Statement statement = null;


        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "DELETE FROM user WHERE UserLogin = '" + user.getLogin() + "'";
            //System.out.println(query);
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public void deleteRoom(Room room) throws SQLException {

        Connection connection = null;
        Statement statement = null;


        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } else {
            statement = connection.createStatement();

            String query = "DELETE FROM rooms WHERE RoomName = " + room.getName();
            //System.out.println(query);
            statement.execute(query);

        }
        connection.close();
        //System.out.println("Connection Closed");
    }

    @Override
    public List<Reservation> getAllReservations(Date from, Date to) throws SQLException {

        int fromMonth = from.getMonth(), fromDay = from.getDate();
        int toMonth = to.getMonth(), toDay = to.getDate();


        ArrayList<Reservation> myReservationsList = new ArrayList();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from reservations WHERE (Month BETWEEN " + (fromMonth + 1) + " and " + (toMonth + 1) + ") and (Day BETWEEN " + fromDay + " and " + toDay + ")";
        System.out.println(query);
        ResultSet rs = statement.executeQuery(query);

        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            Reservation myReservation = new Reservation();
            myReservation.setId(rs.getInt("ReservationID"));
            myReservation.setRoomId(rs.getInt("RoomID"));
            myReservation.setOwnerId(rs.getInt("UserID"));
            myReservation.setYear(rs.getInt("Year"));
            myReservation.setMonth(rs.getInt("Month"));
            myReservation.setDay(rs.getInt("Day"));
            myReservation.setFromHour(rs.getInt("FromHour"));
            myReservation.setToHour(rs.getInt("ToHour"));



            myReservationsList.add(myReservation);
        }
        connection.close();
        return myReservationsList;


    }

    @Override
    public List<Reservation> getAllReservations(Date from, Date to, int roomId) throws SQLException {
        int fromMonth = from.getMonth(), fromDay = from.getDate();
        int toMonth = to.getMonth(), toDay = to.getDate();


        ArrayList<Reservation> myReservationsList = new ArrayList();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return null;
        }

        statement = connection.createStatement();

        String query = "select * from reservations WHERE ((Month BETWEEN " + (fromMonth + 1) + " and " + (toMonth + 1) + ") and (Day BETWEEN " + fromDay + " and " + toDay + ")) and RoomID =" + roomId;
        //System.out.println(query);
        ResultSet rs = statement.executeQuery(query);

        if (rs == null) {
            System.out.println("Error rs");
            return null;
        }

        while (rs.next()) {

            Reservation myReservation = new Reservation();
            myReservation.setId(rs.getInt("ReservationID"));
            myReservation.setRoomId(rs.getInt("RoomID"));
            myReservation.setOwnerId(rs.getInt("UserID"));
            myReservation.setYear(rs.getInt("Year"));
            myReservation.setMonth(rs.getInt("Month"));
            myReservation.setDay(rs.getInt("Day"));
            myReservation.setFromHour(rs.getInt("FromHour"));
            myReservation.setToHour(rs.getInt("ToHour"));



            myReservationsList.add(myReservation);
        }
        connection.close();
        return myReservationsList;
    }

    @Override
    public int getReservationId(Reservation reservation) throws SQLException {

        Reservation myReservation = new Reservation();

        Connection connection = null;
        Statement statement = null;

        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection User");
            return -1;
        }

        statement = connection.createStatement();

        String query = "select ReservationID from reservations where RoomID = " + reservation.getRoomId() + " and UserID = " + reservation.getOwnerId() + " and Year = " + reservation.getYear() + " and Month = " + reservation.getMonth() + " and Day =" + reservation.getDay() + " and FromHour =" + reservation.getFromHour() + " and ToHour =" + reservation.getToHour();

        //System.out.println(query);
        ResultSet rs = statement.executeQuery(query);

        if (rs == null) {
            System.out.println("Error rs");
            return -1;
        }

        while (rs.next()) {

            myReservation.setId(rs.getInt("ReservationID"));


            connection.close();
            //System.out.println("Connection Closed");

            return myReservation.getId();
        }

        return -1;
    }

    @Override
    public int deleteInvitation(InvitationRow removeUnselectedRows) throws SQLException {

        Connection connection = null;
        Statement statement = null;


        connection = DataBaseConnect();

        if (connection == null) {
            System.out.println("Error Connection findUser");

        } 
        
            statement = connection.createStatement();
  
               // if (removeUnselectedRows.getAccepted() == false) {
                    String query = "DELETE FROM invitations WHERE UserID = " + removeUnselectedRows.getInviter() + " and reservationID = " + removeUnselectedRows.getReservationId() + " and title = " + removeUnselectedRows.getTitle() + " and accepted = 0";
                    statement.execute(query);
              //  } else {
                 ///   String query = "UPDATE invitations SET accepted = 1 WHERE reservationID = " + removeUnselectedRows.getReservationId() + " and userID = " + removeUnselectedRows.getInviter() + " and title = " + removeUnselectedRows.getTitle();
                  //  statement.execute(query);


                //}
            //}

        
        connection.close();
        return 0;

    }

    @Override
    public void setInvitationAccepted(int userId, int reservationId) throws SQLException {
        Connection connection = DataBaseConnect();
        Statement statement = connection.createStatement();
        statement = connection.createStatement();
        String query = "UPDATE invitations SET accepted=1 WHERE userID="+userId+" AND reservationId="+reservationId;
        System.out.println(query);
        statement.execute(query);
    }
}
